#pragma once

#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include "../util/dbconn.hh"
#include "../util/config.hh"
#include "../poco/word.hh"

namespace WordMemory
{
    class WordRepository
    {
    public:
        static WordRepository& instance()
        {
            static WordRepository inst;
            return inst;
        }

    public:
        // 获取用户可见的特定单词的详细信息，并检查是否在用户的收藏夹中
        // 返回一个 pair: 第一个是 Word 对象，第二个是 bool (is_favorite)
        std::pair<Word, bool> getWordAndFavoriteStatus(int user_id, const std::string& word_text)
        {
            ConnectionGuard conn;
            try
            {
                // 1. Get word base details and favorite status (mnemonics removed from this main query)
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT "
                        "    w.id, w.word, w.meaning, "
                        "    EXISTS(" // Subquery for favorite status
                        "        SELECT 1 FROM vocabulary_words vw_fav "
                        "        JOIN vocabularies v_fav ON vw_fav.vocabulary_id = v_fav.id "
                        "        WHERE v_fav.user_id = ? AND v_fav.is_favorites = TRUE AND vw_fav.word_id = w.id"
                        "    ) AS is_favorite_status " // Renamed alias for clarity
                        "FROM words w "
                        "JOIN vocabulary_words vw_access ON w.id = vw_access.word_id "
                        "JOIN vocabularies v_access ON vw_access.vocabulary_id = v_access.id "
                        "WHERE v_access.user_id = ? AND w.word = ? "
                        "LIMIT 1" // No GROUP BY needed here anymore for mnemonics
                    ));
                pstmt->setInt(1, user_id); // For is_favorite check
                pstmt->setInt(2, user_id); // For access check
                pstmt->setString(3, word_text);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                if (!res->next()) {
                    spdlog::warn("Word '{}' not found for user_id {} or user does not have access.", word_text, user_id);
                    throw RepositoryExcept("Word not found or access denied");
                }

                int word_id = res->getInt("id");
                Word result_word( // Construct with basic info
                    word_id,
                    res->getString("word"),
                    res->getString("meaning")
                    // examples and mnemonics will be filled next
                );
                bool is_favorite = res->getBoolean("is_favorite_status");

                // 2. Fetch examples
                std::unique_ptr<sql::PreparedStatement> example_stmt(
                    conn.get()->prepareStatement(
                        "SELECT id, example, translation FROM examples WHERE word_id = ?"));
                example_stmt->setInt(1, word_id);
                std::unique_ptr<sql::ResultSet> example_res(example_stmt->executeQuery());
                std::vector<Example> examples_list;
                while (example_res->next())
                {
                    examples_list.emplace_back(
                        res->getInt("id"), // Bugfix: should be example_res->getInt
                        example_res->getString("example"),
                        example_res->getString("translation")
                    );
                }
                result_word.examples = examples_list;

                // 3. Fetch mnemonics
                std::unique_ptr<sql::PreparedStatement> mnemonic_stmt(
                    conn.get()->prepareStatement(
                        "SELECT mnemonic FROM mnemonics WHERE word_id = ? ORDER BY id ASC")); // Order by id or created_at
                mnemonic_stmt->setInt(1, word_id);
                std::unique_ptr<sql::ResultSet> mnemonic_res(mnemonic_stmt->executeQuery());
                std::vector<std::string> mnemonics_list_data; // Renamed
                while (mnemonic_res->next())
                {
                    mnemonics_list_data.push_back(mnemonic_res->getString("mnemonic"));
                }
                result_word.mnemonics = mnemonics_list_data;

                return std::make_pair(result_word, is_favorite);
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get word '{}' and favorite status for user_id {}: {}", word_text, user_id, e.what());
                throw RepositoryExcept("Failed to get word and favorite status: " + std::string(e.what()));
            }
        }

        Word getWordById(int word_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT id, word, meaning FROM words WHERE id = ? LIMIT 1"));
                pstmt->setInt(1, word_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                if (!res->next()) {
                     spdlog::warn("Word with id {} not found globally", word_id);
                    throw RepositoryExcept("Word not found with id: " + std::to_string(word_id));
                }

                Word result_word(
                    res->getInt("id"),
                    res->getString("word"),
                    res->getString("meaning")
                );

                // Fetch examples
                std::unique_ptr<sql::PreparedStatement> example_stmt(
                    conn.get()->prepareStatement(
                        "SELECT id, example, translation FROM examples WHERE word_id = ?"));
                example_stmt->setInt(1, word_id); // Use word_id directly
                std::unique_ptr<sql::ResultSet> example_res(example_stmt->executeQuery());
                std::vector<Example> examples_list;
                while (example_res->next()) {
                    examples_list.emplace_back(example_res->getInt("id"), example_res->getString("example"), example_res->getString("translation"));
                }
                result_word.examples = examples_list;

                // Fetch mnemonics
                std::unique_ptr<sql::PreparedStatement> mnemonic_stmt(
                    conn.get()->prepareStatement(
                        "SELECT mnemonic FROM mnemonics WHERE word_id = ? ORDER BY id ASC"));
                mnemonic_stmt->setInt(1, word_id); // Use word_id directly
                std::unique_ptr<sql::ResultSet> mnemonic_res(mnemonic_stmt->executeQuery());
                std::vector<std::string> mnemonics_list_data;
                while (mnemonic_res->next()) {
                    mnemonics_list_data.push_back(mnemonic_res->getString("mnemonic"));
                }
                result_word.mnemonics = mnemonics_list_data;

                return result_word;
            }
            catch (const sql::SQLException& e) {
                spdlog::error("Failed to get word by id {}: {}", word_id, e.what());
                throw RepositoryExcept("Failed to get word by id: " + std::string(e.what()));
            }
        }

        void addExample(int word_id, const std::string& example, const std::string& translation)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO examples (word_id, example, translation) VALUES (?, ?, ?)"));
                pstmt->setInt(1, word_id);
                pstmt->setString(2, example);
                pstmt->setString(3, translation);
                pstmt->executeUpdate();
                spdlog::info("Added example for word_id {}", word_id);
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to add example for word_id {}: {}",word_id, e.what());
                throw RepositoryExcept("Failed to add example: " + std::string(e.what()));
            }
        }

        void addMnemonic(int word_id, const std::string& mnemonic_text)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO mnemonics (word_id, mnemonic) VALUES (?, ?)"));
                pstmt->setInt(1, word_id);
                pstmt->setString(2, mnemonic_text);
                pstmt->executeUpdate();
                spdlog::info("Added mnemonic for word_id {}: {}", word_id, mnemonic_text);
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to add mnemonic for word_id {}: {}", word_id, e.what());
                throw RepositoryExcept("Failed to add mnemonic: " + std::string(e.what()));
            }
        }

        void updateStatus(int user_id, int word_id, const std::string& status)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO word_status (user_id, word_id, status) "
                        "VALUES (?, ?, ?) "
                        "ON DUPLICATE KEY UPDATE status = ?, updated_at = NOW()"));
                pstmt->setInt(1, user_id);
                pstmt->setInt(2, word_id);
                pstmt->setString(3, status);
                pstmt->setString(4, status);
                pstmt->executeUpdate();
            }
            catch (const sql::SQLException& e)
            {
                throw RepositoryExcept("Failed to update status: " + std::string(e.what()));
            }
        }

    private:
        WordRepository() = default;
        WordRepository(const WordRepository&) = delete;
        WordRepository& operator=(const WordRepository&) = delete;
    };
}